﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using System.IO;
using System.Data;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Util;

namespace CURE.Common
{
    public class Export
    {
        HSSFWorkbook hssfworkbook;
        public void InitializeWorkbook()
        {

            hssfworkbook = new HSSFWorkbook();

            ////create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI Team";
            hssfworkbook.DocumentSummaryInformation = dsi;

            ////create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "NPOI SDK Example";
            hssfworkbook.SummaryInformation = si;
        }

        public MemoryStream WriteToStream()
        {
            MemoryStream file = new MemoryStream();
            hssfworkbook.Write(file);
            return file;
        }

        public void GenerateData(DataTable dt, string[] columns, string title)
        {
            ISheet sheet1 = hssfworkbook.CreateSheet(title);

            #region headtitle设置样式
            ICellStyle headtitlecellstyle = hssfworkbook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            headtitlecellstyle.Alignment = HorizontalAlignment.CENTER;
            //新建一个字体样式对象
            IFont headtitlefont = hssfworkbook.CreateFont();
            //设置字体加粗样式
            headtitlefont.Boldweight = short.MaxValue;
            //设置字体高度
            headtitlefont.FontHeight = 20 * 20;
            //背景颜色
            headtitlecellstyle.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
            headtitlecellstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
            if (headtitlefont != null)
            {
                //使用SetFont方法将字体样式添加到单元格样式中 
                headtitlecellstyle.SetFont(headtitlefont);
            }
            #endregion

            #region headerRow设置样式
            ICellStyle headerRowcellstyle = hssfworkbook.CreateCellStyle();

            //设置单元格的样式：水平对齐居中
            headerRowcellstyle.Alignment = HorizontalAlignment.CENTER;
            //新建一个字体样式对象
            IFont headerRowfont = hssfworkbook.CreateFont();
            //设置字体加粗样式
            headerRowfont.Boldweight = short.MaxValue;
            //背景颜色
            headerRowcellstyle.FillForegroundColor = HSSFColor.LIGHT_TURQUOISE.index;
            headerRowcellstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
            headerRowcellstyle.BorderBottom = BorderStyle.THIN;
            headerRowcellstyle.BorderLeft = BorderStyle.THIN;
            headerRowcellstyle.BorderRight = BorderStyle.THIN;
            headerRowcellstyle.BorderTop = BorderStyle.THIN;
            if (headerRowfont != null)
            {
                //使用SetFont方法将字体样式添加到单元格样式中 
                headerRowcellstyle.SetFont(headerRowfont);
            }
            #endregion

            #region body设置样式
            ICellStyle bodycellstyle = hssfworkbook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            bodycellstyle.Alignment = HorizontalAlignment.CENTER;
            bodycellstyle.BorderBottom = BorderStyle.THIN;
            bodycellstyle.BorderLeft = BorderStyle.THIN;
            bodycellstyle.BorderRight = BorderStyle.THIN;
            bodycellstyle.BorderTop = BorderStyle.THIN;
            #endregion

            #region title
            IRow headtitle = sheet1.CreateRow(0);
            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, columns.Length - 1));
            headtitle.CreateCell(0).SetCellValue(title);
            headtitle.Cells[0].CellStyle = headtitlecellstyle;
            #endregion

            #region header
            int hrowid = 0;
            IRow headerRow = sheet1.CreateRow(1);

            foreach (string column in columns)
            {
                headerRow.CreateCell(hrowid).SetCellValue(column);

                headerRow.Cells[hrowid].CellStyle = headerRowcellstyle;
                sheet1.SetColumnWidth(hrowid, 20 * 256);
                hrowid++;

            }
            #endregion

            #region body
            int rowIndex = 2;
            foreach (DataRow row in dt.Rows)
            {
                IRow dataRow = sheet1.CreateRow(rowIndex);
                foreach (DataColumn column in dt.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    dataRow.Cells[column.Ordinal].CellStyle = bodycellstyle;
                }
                rowIndex++;
            }
            #endregion

        }



        /// 物料在库统计
        /// <summary>
        /// 物料在库统计
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="order">在库单号</param>
        /// <param name="date_ymd">年月日</param>
        /// <param name="date_hms">时分秒</param>
        /// <param name="shift">班次</param>
        public void GenerateDataForAbnormalReport(DataTable dt, Dictionary<string, object> dic, string title)
        {

            ISheet sheet1 = hssfworkbook.CreateSheet(title);
            //ISheet sheet1 = hssfworkbook.CreateSheet("开始时间" + order);

            #region 各列宽度
            sheet1.SetColumnWidth(0, 5000);
            sheet1.SetColumnWidth(1, 6000);
            sheet1.SetColumnWidth(2, 4000);
            sheet1.SetColumnWidth(3, 8000);
            sheet1.SetColumnWidth(4, 8000);
            sheet1.SetColumnWidth(5, 10000);
            //sheet1.SetColumnWidth(6, 8000);
            #endregion

            #region 标题样式
            //新建一个字体样式对象
            IFont fontTitle = hssfworkbook.CreateFont();
            fontTitle.FontHeightInPoints = 18;
            //设置字体加粗样式
            fontTitle.Boldweight = (short)FontBoldWeight.BOLD;
            ICellStyle styleTiltle = hssfworkbook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            styleTiltle.Alignment = HorizontalAlignment.CENTER;
            styleTiltle.SetFont(fontTitle);
            //设置单元格的样式：上下对齐居中
            styleTiltle.VerticalAlignment = VerticalAlignment.CENTER;
            #endregion

            #region 子标题样式
            IFont fontSmallTitle = hssfworkbook.CreateFont();
            fontSmallTitle.FontHeightInPoints = 10;
            fontSmallTitle.Boldweight = (short)FontBoldWeight.BOLD;
            ICellStyle styleSmallTitle = hssfworkbook.CreateCellStyle();
            styleSmallTitle.Alignment = HorizontalAlignment.CENTER;
            styleSmallTitle.SetFont(fontSmallTitle);
            styleSmallTitle.VerticalAlignment = VerticalAlignment.CENTER;
            #endregion

            #region 内容基本样式
            IFont fontContent = hssfworkbook.CreateFont();
            fontContent.FontHeightInPoints = 10;
            ICellStyle styleContent = hssfworkbook.CreateCellStyle();
            styleContent.Alignment = HorizontalAlignment.CENTER;
            styleContent.SetFont(fontContent);
            styleContent.VerticalAlignment = VerticalAlignment.CENTER;
            styleContent.BorderBottom = BorderStyle.THIN;
            styleContent.BorderLeft = BorderStyle.THIN;
            styleContent.BorderRight = BorderStyle.THIN;
            styleContent.BorderTop = BorderStyle.THIN;
            #endregion

            #region 底部样式
            IFont fontFooter = hssfworkbook.CreateFont();
            fontFooter.FontHeightInPoints = 10;
            fontFooter.Boldweight = (short)FontBoldWeight.BOLD;
            ICellStyle styleFooter = hssfworkbook.CreateCellStyle();
            styleFooter.Alignment = HorizontalAlignment.CENTER;
            styleFooter.SetFont(fontFooter);
            styleFooter.VerticalAlignment = VerticalAlignment.CENTER;
            styleFooter.BorderBottom = BorderStyle.THIN;
            styleFooter.BorderLeft = BorderStyle.THIN;
            styleFooter.BorderRight = BorderStyle.THIN;
            styleFooter.BorderTop = BorderStyle.THIN;
            #endregion

            #region excel
            //第一行
            IRow row0 = sheet1.CreateRow(0);
            //row0.HeightInPoints = 45;

            //row0.CreateCell(0).SetCellValue("FM15-00-01");
            //sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 1));
            //row0.GetCell(0).CellStyle = styleSmallTitle;

            row0.CreateCell(2).SetCellValue(title);
            sheet1.AddMergedRegion(new CellRangeAddress(0, 2, 2, 3));
            row0.GetCell(2).CellStyle = styleTiltle;

            //信息
            IRow row3 = sheet1.CreateRow(3);
            row3.HeightInPoints = 20;

            row3.CreateCell(0).SetCellValue("开始时间：" + dic["StartDate"]);
            sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 0, 1));
            row3.GetCell(0).CellStyle = styleSmallTitle;

            row3.CreateCell(2).SetCellValue("结束时间：" + dic["EndDate"]);
            sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 2, 3));
            row3.GetCell(2).CellStyle = styleSmallTitle;

            //row3.CreateCell(4).SetCellValue("时间：" + date_hms);
            //sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 4, 4));
            //row3.GetCell(4).CellStyle = styleSmallTitle;

            //row3.CreateCell(5).SetCellValue("入库班：" + shift);
            //sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 5, 5));
            //row3.GetCell(5).CellStyle = styleSmallTitle;

            //小表头
            IRow row5 = sheet1.CreateRow(5);
            row5.HeightInPoints = 26;

            row5.CreateCell(0).SetCellValue("序号");
            row5.GetCell(0).CellStyle = styleFooter;

            row5.CreateCell(1).SetCellValue("班次");
            row5.GetCell(1).CellStyle = styleFooter;

            row5.CreateCell(2).SetCellValue("机台号");
            row5.GetCell(2).CellStyle = styleFooter;

            row5.CreateCell(3).SetCellValue("规格");
            row5.GetCell(3).CellStyle = styleFooter;

            row5.CreateCell(4).SetCellValue("时间");
            row5.GetCell(4).CellStyle = styleFooter;

            row5.CreateCell(5).SetCellValue("异常信息");
            row5.GetCell(5).CellStyle = styleFooter;

            //sheet1.AddMergedRegion(new CellRangeAddress(5, 5, 3, 4));

            //row5.CreateCell(4).SetCellValue("");
            //row5.GetCell(4).CellStyle = styleFooter;

            //row5.CreateCell(4).SetCellValue("备注");
            //row5.GetCell(4).CellStyle = styleFooter;

            //row5.CreateCell(5).SetCellValue("备注");
            //row5.GetCell(5).CellStyle = styleFooter;

            //填充数据
            int rowNumRe = 6;
            int li = dt.Rows.Count;
            int sum = 0;

            for (int i = 0; i < li; i++)
            {
                IRow rowInfo = sheet1.CreateRow(rowNumRe);
                rowInfo.HeightInPoints = 22;

                string id = dt.Rows[i][0].ToString();//序号
                string shift = dt.Rows[i][1].ToString();//班次
                string cure_mc_no = dt.Rows[i][2].ToString();//机台号
                //string amount = dt.Rows[i][2].ToString();//数量
                //sum += int.Parse(amount);
                string production_code = dt.Rows[i][3].ToString();//班次
                string alarm_code = dt.Rows[i][4].ToString();//班次
                string alarm_date = dt.Rows[i][5].ToString();//班次
               
                //string shift1 = dt.Rows[i][4].ToString();//时间

               
                
               

                rowInfo.CreateCell(0).SetCellValue(id);
                rowInfo.CreateCell(1).SetCellValue(shift);
                rowInfo.CreateCell(2).SetCellValue(cure_mc_no);
                rowInfo.CreateCell(3).SetCellValue(production_code);         
            
                rowInfo.CreateCell(4).SetCellValue(alarm_date);
                rowInfo.CreateCell(5).SetCellValue(alarm_code);


                //sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe, rowNumRe, 3, 4));

                rowInfo.GetCell(0).CellStyle = styleContent;
                rowInfo.GetCell(1).CellStyle = styleContent;
                rowInfo.GetCell(2).CellStyle = styleContent;
                rowInfo.GetCell(3).CellStyle = styleContent;
                rowInfo.GetCell(4).CellStyle = styleContent;
                rowInfo.GetCell(5).CellStyle = styleContent;
                //rowInfo.GetCell(6).CellStyle = styleContent;

                rowNumRe++;
            }

            //第rowNumRe行
            IRow rowN = sheet1.CreateRow(rowNumRe);
            rowN.HeightInPoints = 20;
            sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe, rowNumRe, 0, 1));

            //rowN.CreateCell(0).SetCellValue("合计");
            //rowN.GetCell(0).CellStyle = styleFooter;

            //rowN.CreateCell(1).SetCellValue("");
            //rowN.GetCell(1).CellStyle = styleFooter;

            //sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe, rowNumRe, 2, 4));

            //rowN.CreateCell(2).SetCellValue(sum);
            //rowN.GetCell(2).CellStyle = styleFooter;

            //rowN.CreateCell(3).SetCellValue("");
            //rowN.GetCell(3).CellStyle = styleFooter;

            //rowN.CreateCell(4).SetCellValue("");
            //rowN.GetCell(4).CellStyle = styleFooter;

            //rowN.CreateCell(5).SetCellValue("");
            //rowN.GetCell(5).CellStyle = styleFooter;

            //rowN.CreateCell(6).SetCellValue("");
            //rowN.GetCell(6).CellStyle = styleFooter;

            //第rowNumRe+2行
            //IRow rowM = sheet1.CreateRow(rowNumRe + 2);
            //rowM.HeightInPoints = 22;

            ////rowM.CreateCell(0).SetCellValue("物流课职长");
            ////sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe + 2, rowNumRe + 2, 0, 1));
            ////rowM.GetCell(0).CellStyle = styleFooter;

            //rowM.CreateCell(1).SetCellValue("");
            //rowM.GetCell(1).CellStyle = styleFooter;

            ////rowM.CreateCell(2).SetCellValue("质量课职长");
            ////sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe + 2, rowNumRe + 2, 2, 3));
            ////rowM.GetCell(2).CellStyle = styleFooter;

            //rowM.CreateCell(3).SetCellValue("");
            //rowM.GetCell(3).CellStyle = styleFooter;

            ////第rowNumRe+3行
            //IRow rowO = sheet1.CreateRow(rowNumRe + 3);
            //rowO.HeightInPoints = 70;

            //rowO.CreateCell(0).SetCellValue("");
            //sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe + 3, rowNumRe + 3, 0, 1));
            //rowO.GetCell(0).CellStyle = styleFooter;

            //rowO.CreateCell(1).SetCellValue("");
            //rowO.GetCell(1).CellStyle = styleFooter;

            //rowO.CreateCell(2).SetCellValue("");
            //sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe + 3, rowNumRe + 3, 2, 3));
            //rowO.GetCell(2).CellStyle = styleFooter;

            //rowO.CreateCell(3).SetCellValue("");
            //rowO.GetCell(3).CellStyle = styleFooter;

            #endregion
        }




        /// 导出月报
        /// <summary>
        /// 导出月报
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="title">标题</param>
        public void GenerateDataForMonthReport(DataTable dt, string title)
        {
            ISheet sheet1 = hssfworkbook.CreateSheet(title);

            #region headtitle设置样式
            ICellStyle headtitlecellstyle = hssfworkbook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            headtitlecellstyle.Alignment = HorizontalAlignment.CENTER;
            headtitlecellstyle.VerticalAlignment = VerticalAlignment.CENTER;
            //新建一个字体样式对象
            IFont headtitlefont = hssfworkbook.CreateFont();
            //设置字体加粗样式
            headtitlefont.Boldweight = short.MaxValue;
            //设置字体高度
            headtitlefont.FontHeight = 20 * 20;
            //背景颜色
            //headtitlecellstyle.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
            headtitlecellstyle.FillForegroundColor = HSSFColor.WHITE.index;
            headtitlecellstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
            if (headtitlefont != null)
            {
                //使用SetFont方法将字体样式添加到单元格样式中 
                headtitlecellstyle.SetFont(headtitlefont);
            }
            #endregion

            #region headerRowcellstyle2设置样式
            ICellStyle headerRowcellstyle2 = hssfworkbook.CreateCellStyle();

            //设置单元格的样式：水平对齐居中
            headerRowcellstyle2.Alignment = HorizontalAlignment.CENTER;
            headerRowcellstyle2.VerticalAlignment = VerticalAlignment.CENTER;
            //新建一个字体样式对象
            IFont headerRowfont2 = hssfworkbook.CreateFont();
            //设置字体加粗样式
            headerRowfont2.Boldweight = short.MaxValue;
            //背景颜色
            headerRowcellstyle2.FillForegroundColor = HSSFColor.WHITE.index;
            headerRowcellstyle2.FillPattern = FillPatternType.SOLID_FOREGROUND;
            if (headerRowfont2 != null)
            {
                //使用SetFont方法将字体样式添加到单元格样式中 
                headerRowcellstyle2.SetFont(headerRowfont2);
            }
            #endregion

            #region headerRow设置样式
            ICellStyle headerRowcellstyle = hssfworkbook.CreateCellStyle();

            //设置单元格的样式：水平对齐居中
            headerRowcellstyle.Alignment = HorizontalAlignment.CENTER;
            headerRowcellstyle.VerticalAlignment = VerticalAlignment.CENTER;
            //新建一个字体样式对象
            IFont headerRowfont = hssfworkbook.CreateFont();
            //设置字体加粗样式
            headerRowfont.Boldweight = short.MaxValue;
            //背景颜色
            //headerRowcellstyle.FillForegroundColor = HSSFColor.LIGHT_TURQUOISE.index;
            headerRowcellstyle.FillForegroundColor = HSSFColor.WHITE.index;
            headerRowcellstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
            headerRowcellstyle.BorderBottom = BorderStyle.THIN;
            headerRowcellstyle.BorderLeft = BorderStyle.THIN;
            headerRowcellstyle.BorderRight = BorderStyle.THIN;
            headerRowcellstyle.BorderTop = BorderStyle.THIN;
            if (headerRowfont != null)
            {
                //使用SetFont方法将字体样式添加到单元格样式中 
                headerRowcellstyle.SetFont(headerRowfont);
            }
            #endregion

            #region body设置样式
            ICellStyle bodycellstyle = hssfworkbook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            bodycellstyle.Alignment = HorizontalAlignment.CENTER;
            bodycellstyle.BorderBottom = BorderStyle.THIN;
            bodycellstyle.BorderLeft = BorderStyle.THIN;
            bodycellstyle.BorderRight = BorderStyle.THIN;
            bodycellstyle.BorderTop = BorderStyle.THIN;
            #endregion

            #region title
            IRow headtitle = sheet1.CreateRow(0);
            headtitle.HeightInPoints = 25;
            sheet1.AddMergedRegion(new CellRangeAddress(0, 1, 0, 4));
            headtitle.CreateCell(0).SetCellValue(title);
            headtitle.Cells[0].CellStyle = headtitlecellstyle;

            headtitle.CreateCell(1).SetCellValue("");
            headtitle.GetCell(1).CellStyle = headtitlecellstyle;

            headtitle.CreateCell(2).SetCellValue("");
            headtitle.GetCell(2).CellStyle = headtitlecellstyle;

            headtitle.CreateCell(3).SetCellValue("");
            headtitle.GetCell(3).CellStyle = headtitlecellstyle;

            headtitle.CreateCell(4).SetCellValue("");
            headtitle.GetCell(4).CellStyle = headtitlecellstyle;


            headtitle.CreateCell(5).SetCellValue("811");
            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 5, 5));
            headtitle.Cells[5].CellStyle = headerRowcellstyle;

            headtitle.CreateCell(6).SetCellValue("8112");
            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 6, 6));
            headtitle.Cells[6].CellStyle = headerRowcellstyle;


            headtitle.CreateCell(7).SetCellValue("担当");
            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 7, 7));
            headtitle.Cells[7].CellStyle = headerRowcellstyle;

            IRow headtitle2 = sheet1.CreateRow(1);
            headtitle2.HeightInPoints = 25;
            headtitle2.CreateCell(0).SetCellValue("");
            headtitle2.Cells[0].CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(1).SetCellValue("");
            headtitle2.Cells[1].CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(2).SetCellValue("");
            headtitle2.Cells[2].CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(3).SetCellValue("");
            headtitle2.Cells[3].CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(4).SetCellValue("");
            headtitle2.Cells[4].CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(5).SetCellValue("");
            headtitle2.Cells[5].CellStyle = headerRowcellstyle;

            headtitle2.CreateCell(6).SetCellValue("");
            headtitle2.Cells[6].CellStyle = headerRowcellstyle;

            headtitle2.CreateCell(7).SetCellValue("");
            headtitle2.Cells[7].CellStyle = headerRowcellstyle;



            IRow headtitle3 = sheet1.CreateRow(2);
            headtitle3.HeightInPoints = 25;
            headtitle3.CreateCell(0).SetCellValue("");
            headtitle3.Cells[0].CellStyle = headtitlecellstyle;

            headtitle3.CreateCell(1).SetCellValue("");
            headtitle3.Cells[1].CellStyle = headtitlecellstyle;

            headtitle3.CreateCell(2).SetCellValue("");
            headtitle3.Cells[2].CellStyle = headtitlecellstyle;

            headtitle3.CreateCell(3).SetCellValue("");
            headtitle3.Cells[3].CellStyle = headtitlecellstyle;

            headtitle3.CreateCell(4).SetCellValue("(BSSY品)");
            headtitle3.Cells[4].CellStyle = headerRowcellstyle2;

            headtitle3.CreateCell(5).SetCellValue("");
            headtitle3.Cells[5].CellStyle = headtitlecellstyle;

            headtitle3.CreateCell(6).SetCellValue("");
            headtitle3.Cells[6].CellStyle = headtitlecellstyle;

            headtitle3.CreateCell(7).SetCellValue("");
            headtitle3.Cells[7].CellStyle = headtitlecellstyle;


            #endregion

            #region header

            #region 第一行
            IRow row1 = sheet1.CreateRow(3);
            row1.HeightInPoints = 20;

            row1.CreateCell(0).SetCellValue("规格");
            sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 0, 2));
            sheet1.SetColumnWidth(0, 20 * 256);
            sheet1.SetColumnWidth(1, 20 * 256);
            sheet1.SetColumnWidth(2, 20 * 256);
            row1.GetCell(0).CellStyle = headerRowcellstyle;
            row1.CreateCell(1).SetCellValue("");
            row1.GetCell(1).CellStyle = headerRowcellstyle;
            row1.CreateCell(2).SetCellValue("");
            row1.GetCell(2).CellStyle = headerRowcellstyle;

            row1.CreateCell(3).SetCellValue("月初库存");
            sheet1.AddMergedRegion(new CellRangeAddress(3, 4, 3, 3));
            sheet1.SetColumnWidth(3, 20 * 256);
            row1.GetCell(3).CellStyle = headerRowcellstyle;

            row1.CreateCell(4).SetCellValue("本月入库");
            sheet1.AddMergedRegion(new CellRangeAddress(3, 4, 4, 4));
            sheet1.SetColumnWidth(4, 20 * 256);
            row1.GetCell(4).CellStyle = headerRowcellstyle;

            row1.CreateCell(5).SetCellValue("本月BSCN出库");
            sheet1.AddMergedRegion(new CellRangeAddress(3, 4, 5, 5));
            sheet1.SetColumnWidth(5, 20 * 256);
            row1.GetCell(5).CellStyle = headerRowcellstyle;

            row1.CreateCell(6).SetCellValue("本月BSSY出库");
            sheet1.AddMergedRegion(new CellRangeAddress(3, 4, 6, 6));
            sheet1.SetColumnWidth(6, 20 * 256);
            row1.GetCell(6).CellStyle = headerRowcellstyle;

            row1.CreateCell(7).SetCellValue("月末库存");
            sheet1.SetColumnWidth(7, 20 * 256);
            sheet1.AddMergedRegion(new CellRangeAddress(3, 4, 7, 7));
            row1.GetCell(7).CellStyle = headerRowcellstyle;

            #endregion

            #region 第二行
            IRow row2 = sheet1.CreateRow(4);
            row2.HeightInPoints = 20;

            row2.CreateCell(0).SetCellValue("物料号");
            row2.GetCell(0).CellStyle = headerRowcellstyle;

            row2.CreateCell(1).SetCellValue("制品编号");
            row2.GetCell(1).CellStyle = headerRowcellstyle;

            row2.CreateCell(2).SetCellValue("等级");
            row2.GetCell(2).CellStyle = headerRowcellstyle;

            row2.CreateCell(3).SetCellValue("");
            row2.GetCell(3).CellStyle = headerRowcellstyle;

            row2.CreateCell(4).SetCellValue("");
            row2.GetCell(4).CellStyle = headerRowcellstyle;

            row2.CreateCell(5).SetCellValue("");
            row2.GetCell(5).CellStyle = headerRowcellstyle;

            row2.CreateCell(6).SetCellValue("");
            row2.GetCell(6).CellStyle = headerRowcellstyle;

            row2.CreateCell(7).SetCellValue("");
            row2.GetCell(7).CellStyle = headerRowcellstyle;
            #endregion

            #endregion

            #region body
            int rowIndex = 5;
            foreach (DataRow row in dt.Rows)
            {
                IRow dataRow = sheet1.CreateRow(rowIndex);
                foreach (DataColumn column in dt.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    dataRow.Cells[column.Ordinal].CellStyle = bodycellstyle;
                }

                rowIndex++;
            }
            #endregion
        }

        /// 导出日报
        /// <summary>
        /// 导出日报
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="title">标题</param>
        public void GenerateDataForDayReport(DataTable dt, string title)
        {
            ISheet sheet1 = hssfworkbook.CreateSheet(title);

            #region headtitle设置样式
            ICellStyle headtitlecellstyle = hssfworkbook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            headtitlecellstyle.Alignment = HorizontalAlignment.CENTER;
            headtitlecellstyle.VerticalAlignment = VerticalAlignment.CENTER;
            //新建一个字体样式对象
            IFont headtitlefont = hssfworkbook.CreateFont();
            //设置字体加粗样式
            headtitlefont.Boldweight = short.MaxValue;
            //设置字体高度
            headtitlefont.FontHeight = 20 * 20;
            //背景颜色
            //headtitlecellstyle.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
            headtitlecellstyle.FillForegroundColor = HSSFColor.WHITE.index;
            headtitlecellstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
            if (headtitlefont != null)
            {
                //使用SetFont方法将字体样式添加到单元格样式中 
                headtitlecellstyle.SetFont(headtitlefont);
            }
            #endregion

            #region headerRowcellstyle2设置样式
            ICellStyle headerRowcellstyle2 = hssfworkbook.CreateCellStyle();

            //设置单元格的样式：水平对齐居中
            headerRowcellstyle2.Alignment = HorizontalAlignment.CENTER;
            headerRowcellstyle2.VerticalAlignment = VerticalAlignment.CENTER;
            //新建一个字体样式对象
            IFont headerRowfont2 = hssfworkbook.CreateFont();
            //设置字体加粗样式
            headerRowfont2.Boldweight = short.MaxValue;
            //背景颜色
            headerRowcellstyle2.FillForegroundColor = HSSFColor.WHITE.index;
            headerRowcellstyle2.FillPattern = FillPatternType.SOLID_FOREGROUND;
            if (headerRowfont2 != null)
            {
                //使用SetFont方法将字体样式添加到单元格样式中 
                headerRowcellstyle2.SetFont(headerRowfont2);
            }
            #endregion

            #region headerRow设置样式
            ICellStyle headerRowcellstyle = hssfworkbook.CreateCellStyle();

            //设置单元格的样式：水平对齐居中
            headerRowcellstyle.Alignment = HorizontalAlignment.CENTER;
            headerRowcellstyle.VerticalAlignment = VerticalAlignment.CENTER;
            //新建一个字体样式对象
            IFont headerRowfont = hssfworkbook.CreateFont();
            //设置字体加粗样式
            headerRowfont.Boldweight = short.MaxValue;
            //背景颜色
            //headerRowcellstyle.FillForegroundColor = HSSFColor.LIGHT_TURQUOISE.index;
            headerRowcellstyle.FillForegroundColor = HSSFColor.WHITE.index;
            headerRowcellstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
            headerRowcellstyle.BorderBottom = BorderStyle.THIN;
            headerRowcellstyle.BorderLeft = BorderStyle.THIN;
            headerRowcellstyle.BorderRight = BorderStyle.THIN;
            headerRowcellstyle.BorderTop = BorderStyle.THIN;
            if (headerRowfont != null)
            {
                //使用SetFont方法将字体样式添加到单元格样式中 
                headerRowcellstyle.SetFont(headerRowfont);
            }
            #endregion

            #region body设置样式
            ICellStyle bodycellstyle = hssfworkbook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            bodycellstyle.Alignment = HorizontalAlignment.CENTER;
            bodycellstyle.BorderBottom = BorderStyle.THIN;
            bodycellstyle.BorderLeft = BorderStyle.THIN;
            bodycellstyle.BorderRight = BorderStyle.THIN;
            bodycellstyle.BorderTop = BorderStyle.THIN;
            #endregion

            #region title
            IRow headtitle = sheet1.CreateRow(0);
            headtitle.HeightInPoints = 25;
            sheet1.AddMergedRegion(new CellRangeAddress(0, 1, 0, 15));
            headtitle.CreateCell(0).SetCellValue(title);
            headtitle.Cells[0].CellStyle = headtitlecellstyle;

            #region
            headtitle.CreateCell(1).SetCellValue("");
            headtitle.GetCell(1).CellStyle = headtitlecellstyle;

            headtitle.CreateCell(2).SetCellValue("");
            headtitle.GetCell(2).CellStyle = headtitlecellstyle;

            headtitle.CreateCell(3).SetCellValue("");
            headtitle.GetCell(3).CellStyle = headtitlecellstyle;

            headtitle.CreateCell(4).SetCellValue("");
            headtitle.GetCell(4).CellStyle = headtitlecellstyle;

            headtitle.CreateCell(5).SetCellValue("");
            headtitle.GetCell(5).CellStyle = headtitlecellstyle;

            headtitle.CreateCell(6).SetCellValue("");
            headtitle.GetCell(6).CellStyle = headtitlecellstyle;

            headtitle.CreateCell(7).SetCellValue("");
            headtitle.GetCell(7).CellStyle = headtitlecellstyle;

            headtitle.CreateCell(8).SetCellValue("");
            headtitle.GetCell(8).CellStyle = headtitlecellstyle;

            headtitle.CreateCell(9).SetCellValue("");
            headtitle.GetCell(9).CellStyle = headtitlecellstyle;

            headtitle.CreateCell(10).SetCellValue("");
            headtitle.GetCell(10).CellStyle = headtitlecellstyle;

            headtitle.CreateCell(11).SetCellValue("");
            headtitle.GetCell(11).CellStyle = headtitlecellstyle;

            headtitle.CreateCell(12).SetCellValue("");
            headtitle.GetCell(12).CellStyle = headtitlecellstyle;

            headtitle.CreateCell(13).SetCellValue("");
            headtitle.GetCell(13).CellStyle = headtitlecellstyle;

            headtitle.CreateCell(14).SetCellValue("");
            headtitle.GetCell(14).CellStyle = headtitlecellstyle;

            headtitle.CreateCell(15).SetCellValue("");
            headtitle.GetCell(15).CellStyle = headtitlecellstyle;

            headtitle.CreateCell(16).SetCellValue("");
            headtitle.GetCell(16).CellStyle = headtitlecellstyle;

            #endregion
            headtitle.CreateCell(16).SetCellValue("811");
            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 16, 16));
            headtitle.Cells[16].CellStyle = headerRowcellstyle;

            headtitle.CreateCell(17).SetCellValue("8112");
            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 17, 17));
            headtitle.Cells[17].CellStyle = headerRowcellstyle;


            headtitle.CreateCell(18).SetCellValue("担当");
            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 18, 18));
            headtitle.Cells[18].CellStyle = headerRowcellstyle;

            IRow headtitle2 = sheet1.CreateRow(1);
            headtitle2.HeightInPoints = 25;
            headtitle2.CreateCell(0).SetCellValue("");
            headtitle2.Cells[0].CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(1).SetCellValue("");
            headtitle2.GetCell(1).CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(2).SetCellValue("");
            headtitle2.GetCell(2).CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(3).SetCellValue("");
            headtitle2.GetCell(3).CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(4).SetCellValue("");
            headtitle2.GetCell(4).CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(5).SetCellValue("");
            headtitle2.GetCell(5).CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(6).SetCellValue("");
            headtitle2.GetCell(6).CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(7).SetCellValue("");
            headtitle2.GetCell(7).CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(8).SetCellValue("");
            headtitle2.GetCell(8).CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(9).SetCellValue("");
            headtitle2.GetCell(9).CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(10).SetCellValue("");
            headtitle2.GetCell(10).CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(11).SetCellValue("");
            headtitle2.GetCell(11).CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(12).SetCellValue("");
            headtitle2.GetCell(12).CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(13).SetCellValue("");
            headtitle2.GetCell(13).CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(14).SetCellValue("");
            headtitle2.GetCell(14).CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(15).SetCellValue("");
            headtitle2.GetCell(15).CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(16).SetCellValue("");
            headtitle2.GetCell(16).CellStyle = headtitlecellstyle;

            headtitle2.CreateCell(17).SetCellValue("");
            headtitle2.GetCell(17).CellStyle = headerRowcellstyle;

            headtitle2.CreateCell(18).SetCellValue("");
            headtitle2.GetCell(18).CellStyle = headerRowcellstyle;

            ////headtitle2.CreateCell(19).SetCellValue("");
            ////headtitle2.GetCell(19).CellStyle = headerRowcellstyle;


            IRow headtitle3 = sheet1.CreateRow(2);
            headtitle3.HeightInPoints = 10;
            headtitle3.CreateCell(0).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(2, 2, 0, 18));
            headtitle3.Cells[0].CellStyle = headtitlecellstyle;


            #endregion

            #region header



            #region 第一行
            IRow row1 = sheet1.CreateRow(3);
            row1.HeightInPoints = 20;

            row1.CreateCell(0).SetCellValue("规格");
            sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 0, 4));
            row1.GetCell(0).CellStyle = headerRowcellstyle;
            row1.CreateCell(1).SetCellValue("");
            row1.GetCell(1).CellStyle = headerRowcellstyle;
            row1.CreateCell(2).SetCellValue("");
            row1.GetCell(2).CellStyle = headerRowcellstyle;
            //row1.CreateCell(3).SetCellValue("");
            //row1.GetCell(3).CellStyle = headerRowcellstyle;
            row1.CreateCell(3).SetCellValue("");
            row1.GetCell(3).CellStyle = headerRowcellstyle;

            row1.CreateCell(4).SetCellValue("昨日");
            sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 4, 4));
            row1.GetCell(4).CellStyle = headerRowcellstyle;

            row1.CreateCell(5).SetCellValue("入库");
            sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 5, 6));
            row1.GetCell(5).CellStyle = headerRowcellstyle;
            row1.CreateCell(6).SetCellValue("");
            row1.GetCell(6).CellStyle = headerRowcellstyle;

            row1.CreateCell(7).SetCellValue("出库");
            sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 7, 17));
            sheet1.SetColumnWidth(7, 10 * 256);
            row1.GetCell(7).CellStyle = headerRowcellstyle;
            row1.CreateCell(8).SetCellValue("");
            row1.GetCell(8).CellStyle = headerRowcellstyle;
            row1.CreateCell(9).SetCellValue("");
            row1.GetCell(9).CellStyle = headerRowcellstyle;

            row1.CreateCell(10).SetCellValue("");
            row1.GetCell(10).CellStyle = headerRowcellstyle;

            row1.CreateCell(11).SetCellValue("");
            row1.GetCell(11).CellStyle = headerRowcellstyle;

            row1.CreateCell(12).SetCellValue("");
            row1.GetCell(12).CellStyle = headerRowcellstyle;

            row1.CreateCell(13).SetCellValue("");
            row1.GetCell(13).CellStyle = headerRowcellstyle;

            row1.CreateCell(14).SetCellValue("");
            row1.GetCell(14).CellStyle = headerRowcellstyle;

            row1.CreateCell(15).SetCellValue("");
            row1.GetCell(15).CellStyle = headerRowcellstyle;

            row1.CreateCell(16).SetCellValue("");
            row1.GetCell(16).CellStyle = headerRowcellstyle;

            row1.CreateCell(17).SetCellValue("");
            row1.GetCell(17).CellStyle = headerRowcellstyle;

            row1.CreateCell(18).SetCellValue("当日库存");
            sheet1.AddMergedRegion(new CellRangeAddress(3, 7, 18, 18));
            row1.GetCell(18).CellStyle = headerRowcellstyle;

            #endregion

            #region 第二行
            IRow row2 = sheet1.CreateRow(4);
            row2.HeightInPoints = 20;

            row2.CreateCell(0).SetCellValue("物料编号");
            sheet1.AddMergedRegion(new CellRangeAddress(4, 7, 0, 0));
            row2.GetCell(0).CellStyle = headerRowcellstyle;

            row2.CreateCell(1).SetCellValue("制品编号");
            sheet1.AddMergedRegion(new CellRangeAddress(4, 7, 1, 1));
            row2.GetCell(1).CellStyle = headerRowcellstyle;

            row2.CreateCell(2).SetCellValue("轮胎规格");
            sheet1.AddMergedRegion(new CellRangeAddress(4, 7, 2, 2));
            row2.GetCell(2).CellStyle = headerRowcellstyle;

            //row2.CreateCell(3).SetCellValue("库位");
            //sheet1.AddMergedRegion(new CellRangeAddress(4, 7, 3, 3));
            //row2.GetCell(3).CellStyle = headerRowcellstyle;

            row2.CreateCell(3).SetCellValue("等级");
            sheet1.AddMergedRegion(new CellRangeAddress(4, 7, 3, 3));
            row2.GetCell(3).CellStyle = headerRowcellstyle;

            row2.CreateCell(4).SetCellValue("库存");
            sheet1.AddMergedRegion(new CellRangeAddress(4, 7, 4, 4));
            row2.GetCell(4).CellStyle = headerRowcellstyle;

            row2.CreateCell(5).SetCellValue("当日入库");
            sheet1.AddMergedRegion(new CellRangeAddress(4, 7, 5, 5));
            row2.GetCell(5).CellStyle = headerRowcellstyle;

            row2.CreateCell(6).SetCellValue("当月累计");
            sheet1.AddMergedRegion(new CellRangeAddress(4, 7, 6, 6));
            row2.GetCell(6).CellStyle = headerRowcellstyle;


            row2.CreateCell(7).SetCellValue("当日");
            sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 7, 16));
            row2.GetCell(7).CellStyle = headerRowcellstyle;


            row2.CreateCell(8).SetCellValue("");
            row2.GetCell(8).CellStyle = headerRowcellstyle;


            row2.CreateCell(9).SetCellValue("");
            row2.GetCell(9).CellStyle = headerRowcellstyle;

            row2.CreateCell(10).SetCellValue("");
            row2.GetCell(10).CellStyle = headerRowcellstyle;

            row2.CreateCell(11).SetCellValue("");
            row2.GetCell(11).CellStyle = headerRowcellstyle;

            row2.CreateCell(12).SetCellValue("");
            row2.GetCell(12).CellStyle = headerRowcellstyle;

            row2.CreateCell(13).SetCellValue("");
            row2.GetCell(13).CellStyle = headerRowcellstyle;

            row2.CreateCell(14).SetCellValue("");
            row2.GetCell(14).CellStyle = headerRowcellstyle;

            row2.CreateCell(15).SetCellValue("");
            row2.GetCell(15).CellStyle = headerRowcellstyle;

            row2.CreateCell(16).SetCellValue("");
            row2.GetCell(16).CellStyle = headerRowcellstyle;



            row2.CreateCell(17).SetCellValue("当月累计");
            sheet1.AddMergedRegion(new CellRangeAddress(4, 7, 17, 17));
            row2.GetCell(17).CellStyle = headerRowcellstyle;

            row2.CreateCell(18).SetCellValue("");
            row2.GetCell(18).CellStyle = headerRowcellstyle;

            #endregion

            #region 第三行
            IRow row3 = sheet1.CreateRow(5);
            row3.HeightInPoints = 20;

            row3.CreateCell(0).SetCellValue("");
            row3.GetCell(0).CellStyle = headerRowcellstyle;

            row3.CreateCell(1).SetCellValue("");
            row3.GetCell(1).CellStyle = headerRowcellstyle;

            row3.CreateCell(2).SetCellValue("");
            row3.GetCell(2).CellStyle = headerRowcellstyle;

            //row3.CreateCell(3).SetCellValue("");

            //row3.GetCell(3).CellStyle = headerRowcellstyle;

            row3.CreateCell(3).SetCellValue("");
            row3.GetCell(3).CellStyle = headerRowcellstyle;

            row3.CreateCell(4).SetCellValue("");
            row3.GetCell(4).CellStyle = headerRowcellstyle;

            row3.CreateCell(5).SetCellValue("");
            row3.GetCell(5).CellStyle = headerRowcellstyle;

            row3.CreateCell(6).SetCellValue("");
            row3.GetCell(6).CellStyle = headerRowcellstyle;

            row3.CreateCell(7).SetCellValue("BSCN");
            sheet1.AddMergedRegion(new CellRangeAddress(5, 5, 7, 12));
            row3.GetCell(7).CellStyle = headerRowcellstyle;

            row3.CreateCell(8).SetCellValue("");
            row3.GetCell(8).CellStyle = headerRowcellstyle;


            row3.CreateCell(9).SetCellValue("");
            row3.GetCell(9).CellStyle = headerRowcellstyle;

            row3.CreateCell(10).SetCellValue("");
            row3.GetCell(10).CellStyle = headerRowcellstyle;

            row3.CreateCell(11).SetCellValue("");
            row3.GetCell(11).CellStyle = headerRowcellstyle;

            row3.CreateCell(12).SetCellValue("");
            row3.GetCell(12).CellStyle = headerRowcellstyle;


            row3.CreateCell(13).SetCellValue("BSSY");
            sheet1.AddMergedRegion(new CellRangeAddress(5, 5, 13, 15));
            row3.GetCell(13).CellStyle = headerRowcellstyle;

            row3.CreateCell(14).SetCellValue("");
            row3.GetCell(14).CellStyle = headerRowcellstyle;

            row3.CreateCell(15).SetCellValue("");
            row3.GetCell(15).CellStyle = headerRowcellstyle;


            row3.CreateCell(16).SetCellValue("出库");
            sheet1.AddMergedRegion(new CellRangeAddress(5, 7, 16, 16));
            row3.GetCell(16).CellStyle = headerRowcellstyle;

            row3.CreateCell(17).SetCellValue("");
            row3.GetCell(17).CellStyle = headerRowcellstyle;

            row3.CreateCell(18).SetCellValue("");
            row3.GetCell(18).CellStyle = headerRowcellstyle;
            #endregion

            #region 第四行
            IRow row4 = sheet1.CreateRow(6);
            row4.HeightInPoints = 20;

            row4.CreateCell(0).SetCellValue("");
            row4.GetCell(0).CellStyle = headerRowcellstyle;

            row4.CreateCell(1).SetCellValue("");
            row4.GetCell(1).CellStyle = headerRowcellstyle;

            row4.CreateCell(2).SetCellValue("");
            row4.GetCell(2).CellStyle = headerRowcellstyle;

            //row4.CreateCell(3).SetCellValue("");

            //row4.GetCell(3).CellStyle = headerRowcellstyle;

            row4.CreateCell(3).SetCellValue("");
            row4.GetCell(3).CellStyle = headerRowcellstyle;

            row4.CreateCell(4).SetCellValue("");
            row4.GetCell(4).CellStyle = headerRowcellstyle;

            row4.CreateCell(5).SetCellValue("");
            row4.GetCell(5).CellStyle = headerRowcellstyle;

            row4.CreateCell(6).SetCellValue("");
            row4.GetCell(6).CellStyle = headerRowcellstyle;

            row4.CreateCell(7).SetCellValue("销售");
            sheet1.AddMergedRegion(new CellRangeAddress(6, 6, 7, 8));
            row4.GetCell(7).CellStyle = headerRowcellstyle;

            row4.CreateCell(8).SetCellValue("");
            row4.GetCell(8).CellStyle = headerRowcellstyle;

            row4.CreateCell(9).SetCellValue("无偿");
            sheet1.AddMergedRegion(new CellRangeAddress(6, 6, 9, 10));
            row4.GetCell(9).CellStyle = headerRowcellstyle;

            row4.CreateCell(10).SetCellValue("");
            row4.GetCell(10).CellStyle = headerRowcellstyle;

            row4.CreateCell(11).SetCellValue("试验");
            sheet1.AddMergedRegion(new CellRangeAddress(6, 7, 11, 11));
            row4.GetCell(11).CellStyle = headerRowcellstyle;

            row4.CreateCell(12).SetCellValue("CN责任/索赔");
            sheet1.AddMergedRegion(new CellRangeAddress(6, 7, 12, 12));
            row4.GetCell(12).CellStyle = headerRowcellstyle;

            row4.CreateCell(13).SetCellValue("工厂责任/索赔/报废");
            sheet1.AddMergedRegion(new CellRangeAddress(6, 7, 13, 13));
            row4.GetCell(13).CellStyle = headerRowcellstyle;

            row4.CreateCell(14).SetCellValue("自主出库");
            sheet1.AddMergedRegion(new CellRangeAddress(6, 7, 14, 14));
            row4.GetCell(14).CellStyle = headerRowcellstyle;

            row4.CreateCell(15).SetCellValue("试验");
            sheet1.AddMergedRegion(new CellRangeAddress(6, 7, 15, 15));
            row4.GetCell(15).CellStyle = headerRowcellstyle;

            row4.CreateCell(16).SetCellValue("");
            row4.GetCell(16).CellStyle = headerRowcellstyle;

            row4.CreateCell(17).SetCellValue("");
            row4.GetCell(17).CellStyle = headerRowcellstyle;

            row4.CreateCell(18).SetCellValue("");
            row4.GetCell(18).CellStyle = headerRowcellstyle;

            #endregion

            #region 第五行
            IRow row5 = sheet1.CreateRow(7);
            row5.HeightInPoints = 20;

            row5.CreateCell(0).SetCellValue("");
            sheet1.SetColumnWidth(0, 10 * 256);
            row5.GetCell(0).CellStyle = headerRowcellstyle;

            row5.CreateCell(1).SetCellValue("");
            sheet1.SetColumnWidth(1, 10 * 256);
            row5.GetCell(1).CellStyle = headerRowcellstyle;

            row5.CreateCell(2).SetCellValue("");
            sheet1.SetColumnWidth(2, 17 * 256);
            row5.GetCell(2).CellStyle = headerRowcellstyle;

            //row5.CreateCell(3).SetCellValue("");
            //sheet1.SetColumnWidth(3, 10 * 256);
            //row5.GetCell(3).CellStyle = headerRowcellstyle;

            row5.CreateCell(3).SetCellValue("");
            sheet1.SetColumnWidth(3, 10 * 256);
            row5.GetCell(3).CellStyle = headerRowcellstyle;

            row5.CreateCell(4).SetCellValue("");
            sheet1.SetColumnWidth(4, 10 * 256);
            row5.GetCell(4).CellStyle = headerRowcellstyle;

            row5.CreateCell(5).SetCellValue("");
            sheet1.SetColumnWidth(5, 10 * 256);
            row5.GetCell(5).CellStyle = headerRowcellstyle;

            row5.CreateCell(6).SetCellValue("");
            sheet1.SetColumnWidth(6, 10 * 256);
            row5.GetCell(6).CellStyle = headerRowcellstyle;

            row5.CreateCell(7).SetCellValue("RE");
            sheet1.SetColumnWidth(7, 10 * 256);
            sheet1.AddMergedRegion(new CellRangeAddress(7, 7, 7, 7));
            row5.GetCell(7).CellStyle = headerRowcellstyle;

            row5.CreateCell(8).SetCellValue("OE");
            sheet1.SetColumnWidth(8, 10 * 256);
            sheet1.AddMergedRegion(new CellRangeAddress(7, 7, 8, 8));
            row5.GetCell(8).CellStyle = headerRowcellstyle;

            row5.CreateCell(9).SetCellValue("RE");
            sheet1.SetColumnWidth(9, 10 * 256);
            sheet1.AddMergedRegion(new CellRangeAddress(7, 7, 9, 9));
            row5.GetCell(9).CellStyle = headerRowcellstyle;

            row5.CreateCell(10).SetCellValue("OE");
            sheet1.SetColumnWidth(10, 10 * 256);
            sheet1.AddMergedRegion(new CellRangeAddress(7, 7, 10, 10));
            row5.GetCell(10).CellStyle = headerRowcellstyle;



            row5.CreateCell(11).SetCellValue(""); sheet1.SetColumnWidth(11, 10 * 256);
            row5.GetCell(11).CellStyle = headerRowcellstyle;

            row5.CreateCell(12).SetCellValue(""); sheet1.SetColumnWidth(12, 12 * 256);
            row5.GetCell(12).CellStyle = headerRowcellstyle;

            row5.CreateCell(13).SetCellValue(""); sheet1.SetColumnWidth(13, 20 * 256);
            row5.GetCell(13).CellStyle = headerRowcellstyle;

            row5.CreateCell(14).SetCellValue(""); sheet1.SetColumnWidth(14, 10 * 256);
            row5.GetCell(14).CellStyle = headerRowcellstyle;

            row5.CreateCell(15).SetCellValue(""); sheet1.SetColumnWidth(15, 10 * 256);
            row5.GetCell(15).CellStyle = headerRowcellstyle;

            row5.CreateCell(16).SetCellValue(""); sheet1.SetColumnWidth(16, 10 * 256);
            row5.GetCell(16).CellStyle = headerRowcellstyle;

            row5.CreateCell(17).SetCellValue(""); sheet1.SetColumnWidth(17, 10 * 256);
            row5.GetCell(17).CellStyle = headerRowcellstyle;

            row5.CreateCell(18).SetCellValue(""); sheet1.SetColumnWidth(18, 10 * 256);
            row5.GetCell(18).CellStyle = headerRowcellstyle;


            #endregion

            #endregion

            #region body
            int rowIndex = 8;
            foreach (DataRow row in dt.Rows)
            {
                IRow dataRow = sheet1.CreateRow(rowIndex);
                foreach (DataColumn column in dt.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    dataRow.Cells[column.Ordinal].CellStyle = bodycellstyle;
                }
                rowIndex++;
            }
            #endregion
        }

        /// 导出拣配单
        /// <summary>
        /// 导出拣配单
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="shippingdate">出库日期</param>
        /// <param name="orderno">送货单号</param>
        /// <param name="filename">excel名字</param>
        public void GenerateDataForPickingReport(DataTable dt, string shippingdate, string orderno)
        {
            ISheet sheet1 = hssfworkbook.CreateSheet(orderno + "拣配单");

            #region 各列宽度
            sheet1.SetColumnWidth(0, 3000);
            sheet1.SetColumnWidth(1, 5000);
            sheet1.SetColumnWidth(2, 4000);
            sheet1.SetColumnWidth(3, 3000);
            sheet1.SetColumnWidth(4, 3000);
            sheet1.SetColumnWidth(5, 4000);
            sheet1.SetColumnWidth(6, 7000);
            #endregion

            #region 标题样式
            //新建一个字体样式对象
            IFont fontTitle = hssfworkbook.CreateFont();
            fontTitle.FontHeightInPoints = 18;
            //设置字体加粗样式
            fontTitle.Boldweight = (short)FontBoldWeight.BOLD;
            ICellStyle styleTiltle = hssfworkbook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            styleTiltle.Alignment = HorizontalAlignment.CENTER;
            styleTiltle.SetFont(fontTitle);
            //设置单元格的样式：上下对齐居中
            styleTiltle.VerticalAlignment = VerticalAlignment.CENTER;
            #endregion

            #region 子表头样式
            IFont fontSmallTitle = hssfworkbook.CreateFont();
            fontSmallTitle.FontHeightInPoints = 10;
            fontSmallTitle.Boldweight = (short)FontBoldWeight.BOLD;
            ICellStyle styleSmallTitle = hssfworkbook.CreateCellStyle();
            styleSmallTitle.Alignment = HorizontalAlignment.CENTER;
            styleSmallTitle.SetFont(fontSmallTitle);
            styleSmallTitle.VerticalAlignment = VerticalAlignment.CENTER;
            #endregion

            #region 内容基本样式
            IFont fontContent = hssfworkbook.CreateFont();
            fontContent.FontHeightInPoints = 10;
            ICellStyle styleContent = hssfworkbook.CreateCellStyle();
            styleContent.Alignment = HorizontalAlignment.CENTER;
            styleContent.SetFont(fontContent);
            styleContent.VerticalAlignment = VerticalAlignment.CENTER;
            styleContent.BorderBottom = BorderStyle.THIN;
            styleContent.BorderLeft = BorderStyle.THIN;
            styleContent.BorderRight = BorderStyle.THIN;
            styleContent.BorderTop = BorderStyle.THIN;
            #endregion

            #region 底部样式
            IFont fontFooter = hssfworkbook.CreateFont();
            fontFooter.FontHeightInPoints = 10;
            fontFooter.Boldweight = (short)FontBoldWeight.BOLD;
            ICellStyle styleFooter = hssfworkbook.CreateCellStyle();
            styleFooter.Alignment = HorizontalAlignment.CENTER;
            styleFooter.SetFont(fontFooter);
            styleFooter.VerticalAlignment = VerticalAlignment.CENTER;
            styleFooter.BorderBottom = BorderStyle.THIN;
            styleFooter.BorderLeft = BorderStyle.THIN;
            styleFooter.BorderRight = BorderStyle.THIN;
            styleFooter.BorderTop = BorderStyle.THIN;
            #endregion

            #region 特殊样式（单号）
            IFont fontSpecialTitle = hssfworkbook.CreateFont();
            fontSpecialTitle.FontHeightInPoints = 10;
            fontSpecialTitle.Boldweight = (short)FontBoldWeight.BOLD;
            ICellStyle styleSpecialTitle = hssfworkbook.CreateCellStyle();
            styleSpecialTitle.Alignment = HorizontalAlignment.LEFT;
            styleSpecialTitle.SetFont(fontSpecialTitle);
            styleSpecialTitle.VerticalAlignment = VerticalAlignment.CENTER;
            #endregion

            #region excel
            //第一行
            IRow row0 = sheet1.CreateRow(0);
            row0.HeightInPoints = 45;
            string currxzq = "拣配单";
            row0.CreateCell(0).SetCellValue(currxzq);
            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 6));//合并第一行1~7单元格
            row0.GetCell(0).CellStyle = styleTiltle;

            //第二行
            IRow row1 = sheet1.CreateRow(1);
            row1.HeightInPoints = 20;

            row1.CreateCell(0).SetCellValue("日期：");
            sheet1.AddMergedRegion(new CellRangeAddress(1, 1, 0, 0));
            row1.GetCell(0).CellStyle = styleSmallTitle;

            row1.CreateCell(1).SetCellValue(shippingdate);
            sheet1.AddMergedRegion(new CellRangeAddress(1, 1, 1, 1));
            row1.GetCell(1).CellStyle = styleSmallTitle;

            row1.CreateCell(5).SetCellValue("送货单号：");
            sheet1.AddMergedRegion(new CellRangeAddress(1, 1, 5, 5));
            row1.GetCell(5).CellStyle = styleSmallTitle;

            row1.CreateCell(6).SetCellValue(orderno);
            sheet1.AddMergedRegion(new CellRangeAddress(1, 1, 6, 6));
            row1.GetCell(6).CellStyle = styleSmallTitle;

            //第三行
            IRow row3 = sheet1.CreateRow(3);
            row3.HeightInPoints = 26;

            row3.CreateCell(0).SetCellValue("序号");
            row3.GetCell(0).CellStyle = styleFooter;

            row3.CreateCell(1).SetCellValue("物料号");
            row3.GetCell(1).CellStyle = styleFooter;

            row3.CreateCell(2).SetCellValue("制品编号");
            row3.GetCell(2).CellStyle = styleFooter;

            row3.CreateCell(3).SetCellValue("等级");
            row3.GetCell(3).CellStyle = styleFooter;

            row3.CreateCell(4).SetCellValue("数量");
            row3.GetCell(4).CellStyle = styleFooter;

            row3.CreateCell(5).SetCellValue("地址");
            row3.GetCell(5).CellStyle = styleFooter;

            row3.CreateCell(6).SetCellValue("批次");
            row3.GetCell(6).CellStyle = styleFooter;

            //第四行开始填充数据
            int rowNumRe = 4;
            int li = dt.Rows.Count;
            string pickingorders = string.Empty;
            for (int i = 0; i < li; i++)
            {
                IRow rowInfo = sheet1.CreateRow(rowNumRe);
                rowInfo.HeightInPoints = 22;

                string id = (i + 1).ToString();//序列号
                string ipccode = dt.Rows[i][0].ToString();//物料号
                string gtcode = dt.Rows[i][1].ToString();//制品编号
                string saletype = dt.Rows[i][2].ToString();//等级
                string amount = dt.Rows[i][3].ToString();//数量
                string storagebc = dt.Rows[i][4].ToString();//地址
                string lotno = dt.Rows[i][5].ToString();//批次
                string pickingorder = dt.Rows[i][7].ToString();//拣配单

                rowInfo.CreateCell(0).SetCellValue(id);
                rowInfo.CreateCell(1).SetCellValue(ipccode);
                rowInfo.CreateCell(2).SetCellValue(gtcode);
                rowInfo.CreateCell(3).SetCellValue(saletype);
                rowInfo.CreateCell(4).SetCellValue(amount);
                rowInfo.CreateCell(5).SetCellValue(storagebc);
                rowInfo.CreateCell(6).SetCellValue(lotno);

                rowInfo.GetCell(0).CellStyle = styleContent;
                rowInfo.GetCell(1).CellStyle = styleContent;
                rowInfo.GetCell(2).CellStyle = styleContent;
                rowInfo.GetCell(3).CellStyle = styleContent;
                rowInfo.GetCell(4).CellStyle = styleContent;
                rowInfo.GetCell(5).CellStyle = styleContent;
                rowInfo.GetCell(6).CellStyle = styleContent;

                bool match = MatchPickingorder(pickingorders, pickingorder);
                if (match == false)
                {
                    pickingorders = pickingorders + pickingorder + ",";
                }
                rowNumRe++;
            }

            //第三行
            IRow row2 = sheet1.CreateRow(2);
            row2.HeightInPoints = 20;

            row2.CreateCell(0).SetCellValue("单号：");
            row2.GetCell(0).CellStyle = styleSmallTitle;

            row2.CreateCell(1).SetCellValue(pickingorders);
            sheet1.AddMergedRegion(new CellRangeAddress(2, 2, 1, 6));
            row2.GetCell(1).CellStyle = styleSpecialTitle;

            //第rowNumRe+2行
            IRow rowN = sheet1.CreateRow(rowNumRe + 1);
            rowN.HeightInPoints = 50;

            rowN.CreateCell(0).SetCellValue("确认人");
            rowN.GetCell(0).CellStyle = styleFooter;

            rowN.CreateCell(1).SetCellValue("");
            rowN.GetCell(1).CellStyle = styleFooter;

            rowN.CreateCell(2).SetCellValue("车号");
            rowN.GetCell(2).CellStyle = styleFooter;

            sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe + 1, rowNumRe + 1, 3, 4));
            rowN.CreateCell(3).SetCellValue("");
            rowN.CreateCell(4).SetCellValue("");
            rowN.GetCell(3).CellStyle = styleFooter;
            rowN.GetCell(4).CellStyle = styleFooter;

            rowN.CreateCell(5).SetCellValue("合计");
            rowN.GetCell(5).CellStyle = styleFooter;

            rowN.CreateCell(6).SetCellValue("");
            rowN.GetCell(6).CellStyle = styleFooter;
            #endregion
        }

        /// 导出入库单
        /// <summary>
        /// 导出入库单
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="order">入库单号</param>
        /// <param name="date_ymd">年月日</param>
        /// <param name="date_hms">时分秒</param>
        /// <param name="shift">班次</param>
        public void GenerateDataForCollectionReport(DataTable dt, string order, string date_ymd, string date_hms, string shift)
        {
            ISheet sheet1 = hssfworkbook.CreateSheet("入库单" + order);

            #region 各列宽度
            sheet1.SetColumnWidth(0, 2000);
            sheet1.SetColumnWidth(1, 6000);
            sheet1.SetColumnWidth(2, 4000);
            sheet1.SetColumnWidth(3, 5000);
            sheet1.SetColumnWidth(4, 5000);
            sheet1.SetColumnWidth(5, 4000);
            sheet1.SetColumnWidth(6, 8000);
            #endregion

            #region 标题样式
            //新建一个字体样式对象
            IFont fontTitle = hssfworkbook.CreateFont();
            fontTitle.FontHeightInPoints = 18;
            //设置字体加粗样式
            fontTitle.Boldweight = (short)FontBoldWeight.BOLD;
            ICellStyle styleTiltle = hssfworkbook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            styleTiltle.Alignment = HorizontalAlignment.CENTER;
            styleTiltle.SetFont(fontTitle);
            //设置单元格的样式：上下对齐居中
            styleTiltle.VerticalAlignment = VerticalAlignment.CENTER;
            #endregion

            #region 子标题样式
            IFont fontSmallTitle = hssfworkbook.CreateFont();
            fontSmallTitle.FontHeightInPoints = 10;
            fontSmallTitle.Boldweight = (short)FontBoldWeight.BOLD;
            ICellStyle styleSmallTitle = hssfworkbook.CreateCellStyle();
            styleSmallTitle.Alignment = HorizontalAlignment.CENTER;
            styleSmallTitle.SetFont(fontSmallTitle);
            styleSmallTitle.VerticalAlignment = VerticalAlignment.CENTER;
            #endregion

            #region 内容基本样式
            IFont fontContent = hssfworkbook.CreateFont();
            fontContent.FontHeightInPoints = 10;
            ICellStyle styleContent = hssfworkbook.CreateCellStyle();
            styleContent.Alignment = HorizontalAlignment.CENTER;
            styleContent.SetFont(fontContent);
            styleContent.VerticalAlignment = VerticalAlignment.CENTER;
            styleContent.BorderBottom = BorderStyle.THIN;
            styleContent.BorderLeft = BorderStyle.THIN;
            styleContent.BorderRight = BorderStyle.THIN;
            styleContent.BorderTop = BorderStyle.THIN;
            #endregion

            #region 底部样式
            IFont fontFooter = hssfworkbook.CreateFont();
            fontFooter.FontHeightInPoints = 10;
            fontFooter.Boldweight = (short)FontBoldWeight.BOLD;
            ICellStyle styleFooter = hssfworkbook.CreateCellStyle();
            styleFooter.Alignment = HorizontalAlignment.CENTER;
            styleFooter.SetFont(fontFooter);
            styleFooter.VerticalAlignment = VerticalAlignment.CENTER;
            styleFooter.BorderBottom = BorderStyle.THIN;
            styleFooter.BorderLeft = BorderStyle.THIN;
            styleFooter.BorderRight = BorderStyle.THIN;
            styleFooter.BorderTop = BorderStyle.THIN;
            #endregion

            #region excel
            //第一行
            IRow row0 = sheet1.CreateRow(0);
            //row0.HeightInPoints = 45;

            row0.CreateCell(0).SetCellValue("FM15-00-01");
            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 1));
            row0.GetCell(0).CellStyle = styleSmallTitle;

            row0.CreateCell(2).SetCellValue("成品入库传票");
            sheet1.AddMergedRegion(new CellRangeAddress(0, 2, 2, 5));
            row0.GetCell(2).CellStyle = styleTiltle;

            //信息
            IRow row3 = sheet1.CreateRow(3);
            row3.HeightInPoints = 20;

            row3.CreateCell(0).SetCellValue("入库单号：" + order);
            sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 0, 1));
            row3.GetCell(0).CellStyle = styleSmallTitle;

            row3.CreateCell(2).SetCellValue("入库日期：" + date_ymd);
            sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 2, 3));
            row3.GetCell(2).CellStyle = styleSmallTitle;

            row3.CreateCell(4).SetCellValue("时间：" + date_hms);
            sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 4, 4));
            row3.GetCell(4).CellStyle = styleSmallTitle;

            row3.CreateCell(5).SetCellValue("入库班：" + shift);
            sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 5, 5));
            row3.GetCell(5).CellStyle = styleSmallTitle;

            //小表头
            IRow row5 = sheet1.CreateRow(5);
            row5.HeightInPoints = 26;

            row5.CreateCell(0).SetCellValue("等级");
            row5.GetCell(0).CellStyle = styleFooter;

            row5.CreateCell(1).SetCellValue("物料名");
            row5.GetCell(1).CellStyle = styleFooter;

            row5.CreateCell(2).SetCellValue("制品编号");
            row5.GetCell(2).CellStyle = styleFooter;

            row5.CreateCell(3).SetCellValue("物料描述");
            row5.GetCell(3).CellStyle = styleFooter;

            sheet1.AddMergedRegion(new CellRangeAddress(5, 5, 3, 4));

            row5.CreateCell(4).SetCellValue("");
            row5.GetCell(4).CellStyle = styleFooter;

            row5.CreateCell(5).SetCellValue("入库数量");
            row5.GetCell(5).CellStyle = styleFooter;

            row5.CreateCell(6).SetCellValue("备注");
            row5.GetCell(6).CellStyle = styleFooter;

            //填充数据
            int rowNumRe = 6;
            int li = dt.Rows.Count;
            int sum = 0;

            for (int i = 0; i < li; i++)
            {
                IRow rowInfo = sheet1.CreateRow(rowNumRe);
                rowInfo.HeightInPoints = 22;

                string saletype = dt.Rows[i][0].ToString();//等级
                string ipccode = dt.Rows[i][1].ToString();//物料号
                string gtcode = dt.Rows[i][2].ToString();//制品编号
                string msg = dt.Rows[i][3].ToString();//描述
                string amount = dt.Rows[i][4].ToString();//数量
                sum += int.Parse(amount);

                rowInfo.CreateCell(0).SetCellValue(saletype);
                rowInfo.CreateCell(1).SetCellValue(ipccode);
                rowInfo.CreateCell(2).SetCellValue(gtcode);
                rowInfo.CreateCell(3).SetCellValue(msg);
                rowInfo.CreateCell(4).SetCellValue("");
                rowInfo.CreateCell(5).SetCellValue(amount);
                rowInfo.CreateCell(6).SetCellValue("");

                sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe, rowNumRe, 3, 4));

                rowInfo.GetCell(0).CellStyle = styleContent;
                rowInfo.GetCell(1).CellStyle = styleContent;
                rowInfo.GetCell(2).CellStyle = styleContent;
                rowInfo.GetCell(3).CellStyle = styleContent;
                rowInfo.GetCell(4).CellStyle = styleContent;
                rowInfo.GetCell(5).CellStyle = styleContent;
                rowInfo.GetCell(6).CellStyle = styleContent;

                rowNumRe++;
            }

            //第rowNumRe行
            IRow rowN = sheet1.CreateRow(rowNumRe);
            rowN.HeightInPoints = 20;
            sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe, rowNumRe, 0, 4));

            rowN.CreateCell(0).SetCellValue("合计");
            rowN.GetCell(0).CellStyle = styleFooter;

            rowN.CreateCell(1).SetCellValue("");
            rowN.GetCell(1).CellStyle = styleFooter;

            rowN.CreateCell(2).SetCellValue("");
            rowN.GetCell(2).CellStyle = styleFooter;

            rowN.CreateCell(3).SetCellValue("");
            rowN.GetCell(3).CellStyle = styleFooter;

            rowN.CreateCell(4).SetCellValue("");
            rowN.GetCell(4).CellStyle = styleFooter;

            rowN.CreateCell(5).SetCellValue(sum);
            rowN.GetCell(5).CellStyle = styleFooter;

            rowN.CreateCell(6).SetCellValue("");
            rowN.GetCell(6).CellStyle = styleFooter;

            //第rowNumRe+2行
            IRow rowM = sheet1.CreateRow(rowNumRe + 2);
            rowM.HeightInPoints = 22;

            rowM.CreateCell(0).SetCellValue("物流课职长");
            sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe + 2, rowNumRe + 2, 0, 1));
            rowM.GetCell(0).CellStyle = styleFooter;

            rowM.CreateCell(1).SetCellValue("");
            rowM.GetCell(1).CellStyle = styleFooter;

            rowM.CreateCell(2).SetCellValue("质量课职长");
            sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe + 2, rowNumRe + 2, 2, 3));
            rowM.GetCell(2).CellStyle = styleFooter;

            rowM.CreateCell(3).SetCellValue("");
            rowM.GetCell(3).CellStyle = styleFooter;

            //第rowNumRe+3行
            IRow rowO = sheet1.CreateRow(rowNumRe + 3);
            rowO.HeightInPoints = 70;

            rowO.CreateCell(0).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe + 3, rowNumRe + 3, 0, 1));
            rowO.GetCell(0).CellStyle = styleFooter;

            rowO.CreateCell(1).SetCellValue("");
            rowO.GetCell(1).CellStyle = styleFooter;

            rowO.CreateCell(2).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe + 3, rowNumRe + 3, 2, 3));
            rowO.GetCell(2).CellStyle = styleFooter;

            rowO.CreateCell(3).SetCellValue("");
            rowO.GetCell(3).CellStyle = styleFooter;

            #endregion
        }

        /// 导出转储指令
        /// <summary>
        /// 导出转储指令
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="date"></param>
        /// <param name="shift"></param>
        /// <param name="order"></param>
        public void GenerateDataForTransferReport(DataTable dt, string date, string shift, string order)
        {
            ISheet sheet1 = hssfworkbook.CreateSheet("转储指令");

            #region 各列宽度
            sheet1.SetColumnWidth(0, 4000);
            sheet1.SetColumnWidth(1, 2000);
            sheet1.SetColumnWidth(2, 2000);
            sheet1.SetColumnWidth(3, 4000);
            sheet1.SetColumnWidth(4, 4000);
            sheet1.SetColumnWidth(5, 4000);
            sheet1.SetColumnWidth(6, 2000);
            sheet1.SetColumnWidth(7, 2000);
            sheet1.SetColumnWidth(8, 4000);
            sheet1.SetColumnWidth(9, 4000);
            sheet1.SetColumnWidth(10, 2000);
            sheet1.SetColumnWidth(11, 2000);
            sheet1.SetColumnWidth(12, 4000);
            #endregion

            #region 标题样式
            //新建一个字体样式对象
            IFont fontTitle = hssfworkbook.CreateFont();
            fontTitle.FontHeightInPoints = 18;
            //设置字体加粗样式
            fontTitle.Boldweight = (short)FontBoldWeight.BOLD;
            ICellStyle styleTiltle = hssfworkbook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            styleTiltle.Alignment = HorizontalAlignment.CENTER;
            styleTiltle.SetFont(fontTitle);
            //设置单元格的样式：上下对齐居中
            styleTiltle.VerticalAlignment = VerticalAlignment.CENTER;
            #endregion

            #region 子标题样式
            IFont fontSmallTitle = hssfworkbook.CreateFont();
            fontSmallTitle.FontHeightInPoints = 10;
            fontSmallTitle.Boldweight = (short)FontBoldWeight.BOLD;
            ICellStyle styleSmallTitle = hssfworkbook.CreateCellStyle();
            styleSmallTitle.Alignment = HorizontalAlignment.CENTER;
            styleSmallTitle.SetFont(fontSmallTitle);
            styleSmallTitle.VerticalAlignment = VerticalAlignment.CENTER;
            #endregion

            #region 内容样式
            IFont fontContent = hssfworkbook.CreateFont();
            fontContent.FontHeightInPoints = 10;
            ICellStyle styleContent = hssfworkbook.CreateCellStyle();
            styleContent.Alignment = HorizontalAlignment.CENTER;
            styleContent.SetFont(fontContent);
            styleContent.VerticalAlignment = VerticalAlignment.CENTER;
            styleContent.BorderBottom = BorderStyle.THIN;
            styleContent.BorderLeft = BorderStyle.THIN;
            styleContent.BorderRight = BorderStyle.THIN;
            styleContent.BorderTop = BorderStyle.THIN;
            #endregion

            #region 底部样式
            IFont fontFooter = hssfworkbook.CreateFont();
            fontFooter.FontHeightInPoints = 10;
            fontFooter.Boldweight = (short)FontBoldWeight.BOLD;
            ICellStyle styleFooter = hssfworkbook.CreateCellStyle();
            styleFooter.Alignment = HorizontalAlignment.CENTER;
            styleFooter.SetFont(fontFooter);
            styleFooter.VerticalAlignment = VerticalAlignment.CENTER;
            styleFooter.BorderBottom = BorderStyle.THIN;
            styleFooter.BorderLeft = BorderStyle.THIN;
            styleFooter.BorderRight = BorderStyle.THIN;
            styleFooter.BorderTop = BorderStyle.THIN;
            #endregion

            #region body
            //第一行
            IRow row0 = sheet1.CreateRow(0);
            row0.HeightInPoints = 45;
            string currxzq = "BSSY外库转储确认单";
            row0.CreateCell(0).SetCellValue(currxzq);
            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 12));//合并第一行1~12单元格
            row0.GetCell(0).CellStyle = styleTiltle;

            //第二行
            IRow row1 = sheet1.CreateRow(1);
            row1.HeightInPoints = 20;

            row1.CreateCell(0).SetCellValue("日期：" + date);
            sheet1.AddMergedRegion(new CellRangeAddress(1, 1, 0, 3));
            row1.GetCell(0).CellStyle = styleSmallTitle;

            row1.CreateCell(4).SetCellValue("班次：" + shift);
            sheet1.AddMergedRegion(new CellRangeAddress(1, 1, 4, 4));
            row1.GetCell(4).CellStyle = styleSmallTitle;

            row1.CreateCell(7).SetCellValue("状态：");
            sheet1.AddMergedRegion(new CellRangeAddress(1, 1, 7, 10));
            row1.GetCell(7).CellStyle = styleSmallTitle;

            //第三行
            IRow row2 = sheet1.CreateRow(2);
            row2.HeightInPoints = 20;

            row2.CreateCell(0).SetCellValue("转储单号：" + order);
            sheet1.AddMergedRegion(new CellRangeAddress(2, 2, 0, 3));
            row2.GetCell(0).CellStyle = styleFooter;

            row2.CreateCell(4).SetCellValue("运输公司：");
            sheet1.AddMergedRegion(new CellRangeAddress(2, 2, 4, 4));
            row2.GetCell(4).CellStyle = styleSmallTitle;

            row2.CreateCell(7).SetCellValue("车号：");
            sheet1.AddMergedRegion(new CellRangeAddress(2, 2, 7, 10));
            row2.GetCell(7).CellStyle = styleSmallTitle;

            //第四行
            IRow row3 = sheet1.CreateRow(3);
            row3.HeightInPoints = 14;

            row3.CreateCell(0).SetCellValue("物料号");
            sheet1.AddMergedRegion(new CellRangeAddress(3, 4, 0, 1));
            row3.GetCell(0).CellStyle = styleFooter;

            row3.CreateCell(1).SetCellValue("");
            row3.GetCell(1).CellStyle = styleFooter;

            row3.CreateCell(2).SetCellValue("制品编号");
            sheet1.AddMergedRegion(new CellRangeAddress(3, 4, 2, 3));
            row3.GetCell(2).CellStyle = styleFooter;

            row3.CreateCell(3).SetCellValue("");
            row3.GetCell(3).CellStyle = styleFooter;

            row3.CreateCell(4).SetCellValue("数量");
            sheet1.AddMergedRegion(new CellRangeAddress(3, 4, 4, 4));
            row3.GetCell(4).CellStyle = styleFooter;

            row3.CreateCell(5).SetCellValue("转出");
            sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 5, 8));
            row3.GetCell(5).CellStyle = styleFooter;

            row3.CreateCell(6).SetCellValue("");
            row3.GetCell(6).CellStyle = styleFooter;

            row3.CreateCell(7).SetCellValue("");
            row3.GetCell(7).CellStyle = styleFooter;

            row3.CreateCell(8).SetCellValue("");
            row3.GetCell(8).CellStyle = styleFooter;

            row3.CreateCell(9).SetCellValue("转入");
            sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 9, 12));
            row3.GetCell(9).CellStyle = styleFooter;

            row3.CreateCell(10).SetCellValue("");
            row3.GetCell(10).CellStyle = styleFooter;

            row3.CreateCell(11).SetCellValue("");
            row3.GetCell(11).CellStyle = styleFooter;

            row3.CreateCell(12).SetCellValue("");
            row3.GetCell(12).CellStyle = styleFooter;

            //第五行
            IRow row4 = sheet1.CreateRow(4);
            row4.HeightInPoints = 14;

            row4.CreateCell(0).SetCellValue("");
            row4.GetCell(0).CellStyle = styleFooter;

            row4.CreateCell(1).SetCellValue("");
            row4.GetCell(1).CellStyle = styleFooter;

            row4.CreateCell(2).SetCellValue("");
            row4.GetCell(2).CellStyle = styleFooter;

            row4.CreateCell(3).SetCellValue("");
            row4.GetCell(3).CellStyle = styleFooter;

            row4.CreateCell(4).SetCellValue("");
            row4.GetCell(4).CellStyle = styleFooter;

            row4.CreateCell(5).SetCellValue("库位");
            sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 5, 6));
            row4.GetCell(5).CellStyle = styleFooter;

            row4.CreateCell(6).SetCellValue("");
            row4.GetCell(6).CellStyle = styleFooter;

            row4.CreateCell(7).SetCellValue("仓位");
            sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 7, 8));
            row4.GetCell(7).CellStyle = styleFooter;

            row4.CreateCell(8).SetCellValue("");
            row4.GetCell(8).CellStyle = styleFooter;

            row4.CreateCell(9).SetCellValue("库位");
            sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 9, 10));
            row4.GetCell(9).CellStyle = styleFooter;

            row4.CreateCell(10).SetCellValue("");
            row4.GetCell(10).CellStyle = styleFooter;

            row4.CreateCell(11).SetCellValue("仓位");
            sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 11, 12));
            row4.GetCell(11).CellStyle = styleFooter;

            row4.CreateCell(12).SetCellValue("");
            row4.GetCell(12).CellStyle = styleFooter;


            //第六行开始填充数据
            int rowNumRe = 5;
            int li = dt.Rows.Count;
            for (int i = 0; i < li; i++)
            {
                IRow rowInfo = sheet1.CreateRow(rowNumRe);
                rowInfo.HeightInPoints = 22;

                string ipccode = dt.Rows[i][0].ToString();//物料号
                string gtcode = dt.Rows[i][1].ToString();//制品编号
                string amount = dt.Rows[i][2].ToString();//数量
                string fromwarehouse = dt.Rows[i][3].ToString();
                string fromstoragebc = dt.Rows[i][4].ToString();
                string towarehouse = dt.Rows[i][5].ToString();
                string tostoragebc = dt.Rows[i][6].ToString();

                rowInfo.CreateCell(0).SetCellValue(ipccode);
                rowInfo.CreateCell(1).SetCellValue("");
                rowInfo.CreateCell(2).SetCellValue(gtcode);
                rowInfo.CreateCell(3).SetCellValue("");
                rowInfo.CreateCell(4).SetCellValue(amount);
                rowInfo.CreateCell(5).SetCellValue(fromwarehouse);
                rowInfo.CreateCell(6).SetCellValue("");
                rowInfo.CreateCell(7).SetCellValue(fromstoragebc);
                rowInfo.CreateCell(8).SetCellValue("");
                rowInfo.CreateCell(9).SetCellValue(towarehouse);
                rowInfo.CreateCell(10).SetCellValue("");
                rowInfo.CreateCell(11).SetCellValue(tostoragebc);
                rowInfo.CreateCell(12).SetCellValue("");

                rowInfo.GetCell(0).CellStyle = styleContent;
                rowInfo.GetCell(1).CellStyle = styleContent;
                rowInfo.GetCell(2).CellStyle = styleContent;
                rowInfo.GetCell(3).CellStyle = styleContent;
                rowInfo.GetCell(4).CellStyle = styleContent;
                rowInfo.GetCell(5).CellStyle = styleContent;
                rowInfo.GetCell(6).CellStyle = styleContent;
                rowInfo.GetCell(7).CellStyle = styleContent;
                rowInfo.GetCell(8).CellStyle = styleContent;
                rowInfo.GetCell(9).CellStyle = styleContent;
                rowInfo.GetCell(10).CellStyle = styleContent;
                rowInfo.GetCell(11).CellStyle = styleContent;
                rowInfo.GetCell(12).CellStyle = styleContent;

                sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe, rowNumRe, 0, 1));
                sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe, rowNumRe, 2, 3));
                sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe, rowNumRe, 5, 6));
                sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe, rowNumRe, 7, 8));
                sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe, rowNumRe, 9, 10));
                sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe, rowNumRe, 11, 12));

                rowNumRe++;
            }

            //第rowNumRe+1行
            IRow rowN = sheet1.CreateRow(rowNumRe + 1);
            rowN.HeightInPoints = 26;

            rowN.CreateCell(0).SetCellValue("转储担当");
            rowN.GetCell(0).CellStyle = styleFooter;

            rowN.CreateCell(1).SetCellValue("职长");
            sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe + 1, rowNumRe + 1, 1, 2));
            rowN.GetCell(1).CellStyle = styleFooter;

            rowN.CreateCell(2).SetCellValue("");
            rowN.GetCell(2).CellStyle = styleFooter;

            rowN.CreateCell(3).SetCellValue("课长");
            rowN.GetCell(3).CellStyle = styleFooter;

            rowN.CreateCell(5).SetCellValue("运输担当");
            rowN.GetCell(5).CellStyle = styleFooter;

            rowN.CreateCell(6).SetCellValue("承运人");
            sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe + 1, rowNumRe + 1, 6, 7));
            rowN.GetCell(6).CellStyle = styleFooter;

            rowN.CreateCell(7).SetCellValue("");
            rowN.GetCell(7).CellStyle = styleFooter;

            rowN.CreateCell(9).SetCellValue("装车确认");
            rowN.GetCell(9).CellStyle = styleFooter;

            rowN.CreateCell(12).SetCellValue("接收确认");
            rowN.GetCell(12).CellStyle = styleFooter;

            //第rowNumRe+2行
            IRow rowM = sheet1.CreateRow(rowNumRe + 2);
            rowM.HeightInPoints = 70;

            rowM.CreateCell(0).SetCellValue("");
            rowM.GetCell(0).CellStyle = styleFooter;

            rowM.CreateCell(1).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe + 2, rowNumRe + 2, 1, 2));
            rowM.GetCell(1).CellStyle = styleFooter;

            rowM.CreateCell(2).SetCellValue("");
            rowM.GetCell(2).CellStyle = styleFooter;

            rowM.CreateCell(3).SetCellValue("");
            rowM.GetCell(3).CellStyle = styleFooter;

            rowM.CreateCell(5).SetCellValue("");
            rowM.GetCell(5).CellStyle = styleFooter;

            rowM.CreateCell(6).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(rowNumRe + 2, rowNumRe + 2, 6, 7));
            rowM.GetCell(6).CellStyle = styleFooter;

            rowM.CreateCell(7).SetCellValue("");
            rowM.GetCell(7).CellStyle = styleFooter;

            rowM.CreateCell(9).SetCellValue("");
            rowM.GetCell(9).CellStyle = styleFooter;

            rowM.CreateCell(12).SetCellValue("");
            rowM.GetCell(12).CellStyle = styleFooter;
            #endregion

            #region
            #endregion

            #region
            #endregion

            #region
            #endregion

            #region
            #endregion
        }

        /// 导出仓位标识
        /// <summary>
        /// 导出仓位标识
        /// </summary>
        /// <param name="gtcode">规格</param>
        /// <param name="saletype">等级</param>
        /// <param name="size">描述</param>
        /// <param name="storagebc">仓位</param>
        /// <param name="indate">入库日期</param>
        /// <param name="lotno">批次</param>
        /// <param name="starttime">开始日期</param>
        /// <param name="endtime">结束日期</param>
        public void GenerateDataForLotno(string gtcode, string saletype, string size, string storagebc, string indate, string lotno, string starttime, string endtime)
        {
            ISheet sheet1 = hssfworkbook.CreateSheet("仓位标识");

            #region 各列宽度
            sheet1.SetColumnWidth(0, 1000);
            sheet1.SetColumnWidth(1, 3000);
            sheet1.SetColumnWidth(2, 10000);
            sheet1.SetColumnWidth(3, 2500);
            sheet1.SetColumnWidth(4, 6000);
            sheet1.SetColumnWidth(5, 2500);
            sheet1.SetColumnWidth(6, 2500);
            sheet1.SetColumnWidth(7, 1000);
            sheet1.SetColumnWidth(8, 2000);
            sheet1.SetColumnWidth(9, 6000);
            #endregion

            #region 各行宽度
            IRow row0 = sheet1.CreateRow(0);
            row0.HeightInPoints = 10;
            IRow row1 = sheet1.CreateRow(1);
            row1.HeightInPoints = 100;
            IRow row2 = sheet1.CreateRow(2);
            row2.HeightInPoints = 100;
            IRow row3 = sheet1.CreateRow(3);
            row3.HeightInPoints = 100;
            IRow row4 = sheet1.CreateRow(4);
            row4.HeightInPoints = 50;
            IRow row5 = sheet1.CreateRow(5);
            row5.HeightInPoints = 100;
            #endregion

            #region gtcode
            IFont font_black1 = hssfworkbook.CreateFont();
            //字体大小
            font_black1.FontHeightInPoints = 100;
            //字体粗细
            font_black1.Boldweight = (short)FontBoldWeight.BOLD;
            //字体颜色
            //font_black1.Color = HSSFColor.BLUE.index;
            ICellStyle style_black1 = hssfworkbook.CreateCellStyle();
            style_black1.SetFont(font_black1);
            //设置单元格的样式：水平对齐居中
            style_black1.Alignment = HorizontalAlignment.CENTER;
            //设置单元格的样式：垂直对齐居中
            style_black1.VerticalAlignment = VerticalAlignment.CENTER;
            //边框
            style_black1.BorderBottom = BorderStyle.THIN;
            style_black1.BorderLeft = BorderStyle.THIN;
            style_black1.BorderRight = BorderStyle.THIN;
            style_black1.BorderTop = BorderStyle.THIN;
            //背景颜色
            style_black1.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
            style_black1.FillPattern = FillPatternType.SOLID_FOREGROUND;
            //合并
            sheet1.AddMergedRegion(new CellRangeAddress(1, 1, 2, 7));
            //内容
            row1.CreateCell(2).SetCellValue(gtcode);
            row1.GetCell(2).CellStyle = style_black1;
            row1.CreateCell(3).SetCellValue("");
            row1.GetCell(3).CellStyle = style_black1;
            row1.CreateCell(4).SetCellValue("");
            row1.GetCell(4).CellStyle = style_black1;
            row1.CreateCell(5).SetCellValue("");
            row1.GetCell(5).CellStyle = style_black1;
            row1.CreateCell(6).SetCellValue("");
            row1.GetCell(6).CellStyle = style_black1;
            row1.CreateCell(7).SetCellValue("");
            row1.GetCell(7).CellStyle = style_black1;
            #endregion

            #region saletype
            IFont font_blue1 = hssfworkbook.CreateFont();
            //字体大小
            font_blue1.FontHeightInPoints = 60;
            //字体粗细
            font_blue1.Boldweight = (short)FontBoldWeight.BOLD;
            //字体颜色
            font_blue1.Color = HSSFColor.BLUE.index;
            ICellStyle style_blue = hssfworkbook.CreateCellStyle();
            style_blue.SetFont(font_blue1);
            //设置单元格的样式：水平对齐居中
            style_blue.Alignment = HorizontalAlignment.CENTER;
            //设置单元格的样式：垂直对齐居中
            style_blue.VerticalAlignment = VerticalAlignment.CENTER;
            //边框
            style_blue.BorderBottom = BorderStyle.THIN;
            style_blue.BorderLeft = BorderStyle.THIN;
            style_blue.BorderRight = BorderStyle.THIN;
            style_blue.BorderTop = BorderStyle.THIN;
            //背景颜色
            style_blue.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
            style_blue.FillPattern = FillPatternType.SOLID_FOREGROUND;
            //内容
            row1.CreateCell(9).SetCellValue(saletype);
            row1.GetCell(9).CellStyle = style_blue;
            #endregion

            #region size，规格，仓位
            IFont font_black2 = hssfworkbook.CreateFont();
            //字体大小
            font_black2.FontHeightInPoints = 40;
            //字体粗细
            font_black2.Boldweight = (short)FontBoldWeight.BOLD;
            ICellStyle style_black2 = hssfworkbook.CreateCellStyle();
            style_black2.SetFont(font_black2);
            //设置单元格的样式：水平对齐居中
            style_black2.Alignment = HorizontalAlignment.CENTER;
            //设置单元格的样式：垂直对齐居中
            style_black2.VerticalAlignment = VerticalAlignment.CENTER;
            //行自适应
            style_black2.WrapText = true;
            //边框
            style_black2.BorderBottom = BorderStyle.THIN;
            style_black2.BorderLeft = BorderStyle.THIN;
            style_black2.BorderRight = BorderStyle.THIN;
            style_black2.BorderTop = BorderStyle.THIN;
            //合并
            sheet1.AddMergedRegion(new CellRangeAddress(1, 2, 1, 1));
            sheet1.AddMergedRegion(new CellRangeAddress(2, 2, 2, 9));
            //内容
            row1.CreateCell(1).SetCellValue("规格");
            row1.GetCell(1).CellStyle = style_black2;
            row2.CreateCell(1).SetCellValue("");
            row2.GetCell(1).CellStyle = style_black2;

            row3.CreateCell(1).SetCellValue("仓位");
            row3.GetCell(1).CellStyle = style_black2;

            row2.CreateCell(2).SetCellValue(size);
            row2.GetCell(2).CellStyle = style_black2;
            row2.CreateCell(3).SetCellValue("");
            row2.GetCell(3).CellStyle = style_black2;
            row2.CreateCell(4).SetCellValue("");
            row2.GetCell(4).CellStyle = style_black2;
            row2.CreateCell(5).SetCellValue("");
            row2.GetCell(5).CellStyle = style_black2;
            row2.CreateCell(6).SetCellValue("");
            row2.GetCell(6).CellStyle = style_black2;
            row2.CreateCell(7).SetCellValue("");
            row2.GetCell(7).CellStyle = style_black2;
            row2.CreateCell(8).SetCellValue("");
            row2.GetCell(8).CellStyle = style_black2;
            row2.CreateCell(9).SetCellValue("");
            row2.GetCell(9).CellStyle = style_black2;
            #endregion

            #region 批号
            IFont font_black3 = hssfworkbook.CreateFont();
            //字体大小
            font_black3.FontHeightInPoints = 25;
            //字体粗细
            font_black3.Boldweight = (short)FontBoldWeight.BOLD;
            ICellStyle style_black3 = hssfworkbook.CreateCellStyle();
            style_black3.SetFont(font_black3);
            //设置单元格的样式：水平对齐居中
            style_black3.Alignment = HorizontalAlignment.CENTER;
            //设置单元格的样式：垂直对齐居中
            style_black3.VerticalAlignment = VerticalAlignment.CENTER;
            //行自适应
            //style_black3.WrapText = true;
            //边框
            style_black3.BorderBottom = BorderStyle.THIN;
            style_black3.BorderLeft = BorderStyle.THIN;
            style_black3.BorderRight = BorderStyle.THIN;
            style_black3.BorderTop = BorderStyle.THIN;
            //内容
            row4.CreateCell(1).SetCellValue("批号");
            row4.GetCell(1).CellStyle = style_black3;

            #endregion

            #region lotno，入库日期，起始日期，starttime，结束日期，endtime
            IFont font_black4 = hssfworkbook.CreateFont();
            //字体大小
            font_black4.FontHeightInPoints = 20;
            //字体粗细
            font_black4.Boldweight = (short)FontBoldWeight.BOLD;
            ICellStyle style_black4 = hssfworkbook.CreateCellStyle();
            style_black4.SetFont(font_black4);
            //设置单元格的样式：水平对齐居中
            style_black4.Alignment = HorizontalAlignment.CENTER;
            //设置单元格的样式：垂直对齐居中
            style_black4.VerticalAlignment = VerticalAlignment.CENTER;
            //行自适应
            style_black4.WrapText = true;
            //边框
            style_black4.BorderBottom = BorderStyle.THIN;
            style_black4.BorderLeft = BorderStyle.THIN;
            style_black4.BorderRight = BorderStyle.THIN;
            style_black4.BorderTop = BorderStyle.THIN;
            //合并
            sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 4, 5));
            sheet1.AddMergedRegion(new CellRangeAddress(4, 4, 7, 9));
            //内容
            row3.CreateCell(5).SetCellValue("入库日期");
            row3.GetCell(5).CellStyle = style_black4;

            row4.CreateCell(2).SetCellValue(lotno);
            row4.GetCell(2).CellStyle = style_black4;

            row4.CreateCell(3).SetCellValue("起始日期");
            row4.GetCell(3).CellStyle = style_black4;

            row4.CreateCell(4).SetCellValue(starttime);
            row4.GetCell(4).CellStyle = style_black4;
            row4.CreateCell(5).SetCellValue("");
            row4.GetCell(5).CellStyle = style_black4;

            row4.CreateCell(6).SetCellValue("结束日期");
            row4.GetCell(6).CellStyle = style_black4;

            row4.CreateCell(7).SetCellValue(endtime);
            row4.GetCell(7).CellStyle = style_black4;
            row4.CreateCell(8).SetCellValue("");
            row4.GetCell(8).CellStyle = style_black4;
            row4.CreateCell(9).SetCellValue("");
            row4.GetCell(9).CellStyle = style_black4;
            #endregion

            #region storagebc
            IFont font_red1 = hssfworkbook.CreateFont();
            //字体大小
            font_red1.FontHeightInPoints = 50;
            //字体粗细
            font_red1.Boldweight = (short)FontBoldWeight.BOLD;
            //字体颜色
            font_red1.Color = HSSFColor.RED.index;
            ICellStyle style_red1 = hssfworkbook.CreateCellStyle();
            style_red1.SetFont(font_red1);
            //设置单元格的样式：水平对齐居中
            style_red1.Alignment = HorizontalAlignment.CENTER;
            //设置单元格的样式：垂直对齐居中
            style_red1.VerticalAlignment = VerticalAlignment.CENTER;
            //边框
            style_red1.BorderBottom = BorderStyle.THIN;
            style_red1.BorderLeft = BorderStyle.THIN;
            style_red1.BorderRight = BorderStyle.THIN;
            style_red1.BorderTop = BorderStyle.THIN;
            //合并
            sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 2, 4));
            //内容
            row3.CreateCell(2).SetCellValue(storagebc);
            row3.GetCell(2).CellStyle = style_red1;
            row3.CreateCell(3).SetCellValue("");
            row3.GetCell(3).CellStyle = style_red1;
            row3.CreateCell(4).SetCellValue("");
            row3.GetCell(4).CellStyle = style_red1;
            #endregion

            #region 等级，indate
            IFont font_red2 = hssfworkbook.CreateFont();
            //字体大小
            font_red2.FontHeightInPoints = 30;
            //字体粗细
            font_red2.Boldweight = (short)FontBoldWeight.BOLD;
            //字体颜色
            font_red2.Color = HSSFColor.RED.index;
            ICellStyle style_red2 = hssfworkbook.CreateCellStyle();
            style_red2.SetFont(font_red2);
            //行自适应
            style_red2.WrapText = true;
            //设置单元格的样式：水平对齐居中
            style_red2.Alignment = HorizontalAlignment.CENTER;
            //设置单元格的样式：垂直对齐居中
            style_red2.VerticalAlignment = VerticalAlignment.CENTER;
            //边框
            style_red2.BorderBottom = BorderStyle.THIN;
            style_red2.BorderLeft = BorderStyle.THIN;
            style_red2.BorderRight = BorderStyle.THIN;
            style_red2.BorderTop = BorderStyle.THIN;
            //合并
            sheet1.AddMergedRegion(new CellRangeAddress(3, 3, 6, 9));
            //内容
            row1.CreateCell(8).SetCellValue("等级");
            row1.GetCell(8).CellStyle = style_red2;

            row3.CreateCell(6).SetCellValue(indate);
            row3.GetCell(6).CellStyle = style_red2;
            row3.CreateCell(7).SetCellValue("");
            row3.GetCell(7).CellStyle = style_red2;
            row3.CreateCell(8).SetCellValue("");
            row3.GetCell(8).CellStyle = style_red2;
            row3.CreateCell(9).SetCellValue("");
            row3.GetCell(9).CellStyle = style_red2;
            #endregion

            #region 特记事项
            IFont font_red3 = hssfworkbook.CreateFont();
            //字体大小
            font_red3.FontHeightInPoints = 20;
            //字体粗细
            font_red3.Boldweight = (short)FontBoldWeight.BOLD;
            //字体颜色
            font_red3.Color = HSSFColor.RED.index;
            ICellStyle style_red3 = hssfworkbook.CreateCellStyle();
            style_red3.SetFont(font_red3);
            //设置单元格的样式：水平对齐居中
            style_red3.Alignment = HorizontalAlignment.LEFT;
            //设置单元格的样式：垂直对齐居中
            style_red3.VerticalAlignment = VerticalAlignment.TOP;
            //边框
            style_red3.BorderBottom = BorderStyle.THIN;
            style_red3.BorderLeft = BorderStyle.THIN;
            style_red3.BorderRight = BorderStyle.THIN;
            style_red3.BorderTop = BorderStyle.THIN;
            //合并
            sheet1.AddMergedRegion(new CellRangeAddress(5, 5, 1, 9));
            //内容
            row5.CreateCell(1).SetCellValue("特记事项：");
            row5.GetCell(1).CellStyle = style_red3;
            row5.CreateCell(2).SetCellValue("");
            row5.GetCell(2).CellStyle = style_red3;
            row5.CreateCell(3).SetCellValue("");
            row5.GetCell(3).CellStyle = style_red3;
            row5.CreateCell(4).SetCellValue("");
            row5.GetCell(4).CellStyle = style_red3;
            row5.CreateCell(5).SetCellValue("");
            row5.GetCell(5).CellStyle = style_red3;
            row5.CreateCell(6).SetCellValue("");
            row5.GetCell(6).CellStyle = style_red3;
            row5.CreateCell(7).SetCellValue("");
            row5.GetCell(7).CellStyle = style_red3;
            row5.CreateCell(8).SetCellValue("");
            row5.GetCell(8).CellStyle = style_red3;
            row5.CreateCell(9).SetCellValue("");
            row5.GetCell(9).CellStyle = style_red3;

            #endregion
        }

        /// 所有拣配单号
        /// <summary>
        /// 所有拣配单号
        /// </summary>
        /// <param name="orders"></param>
        /// <param name="pickingorder"></param>
        /// <returns></returns>
        public bool MatchPickingorder(string orders, string pickingorder)
        {
            bool result = false;
            string[] arrTemp_pickingorder = orders.Split(',');
            if (arrTemp_pickingorder.Length > 0)
            {
                for (int i = 0; i < arrTemp_pickingorder.Length; i++)
                {
                    if (pickingorder == arrTemp_pickingorder[i].ToString())
                    {
                        result = true;
                    }
                }
            }
            return result;
        }
    }
}